﻿using BrightIdeasSoftware;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BO;
using System.Collections;
using UI.Core;
using System.Data.SqlClient;
namespace UI
{
    public partial class StaffYearReport : ReportFormBase,IMenuForm
    {
        public StaffYearReport()
        {
            InitializeComponent();
        }

        protected override void InitGrid()
        {
            
            MyGrid.Columns.AddRange(new OLVColumn[] { 
             new OLVColumn(FieldHelper.cnName.Company, "CompanyName"),
                 new OLVColumn(FieldHelper.cnName.Center, "CenterName"),
                new OLVColumn(FieldHelper.cnName.Department, "DepartmentName"),
              new OLVColumn(FieldHelper.cnName.Staff, "StaffName"),
               new OLVColumn(FieldHelper.cnName.IsNormal, "IsNormal") {
                    Width = 60, 
                  AspectToStringConverter =   new AspectToStringConverterDelegate(item => item==null||item==DBNull.Value||Convert.ToInt32(item)!=1
                ? "否" :"是"),
               },
                    new OLVColumn("年龄", "StaffAge") { Width = 60,
                       AspectToStringConverter =
                new AspectToStringConverterDelegate(item => item==null||item==DBNull.Value||Convert.ToInt32(item)>100|| 
                Convert.ToInt32(item)<0
                ? "" :item.ToString()),
                    },
                      new OLVColumn("性别", "StaffSex") { Width = 60,},
             new OLVColumn(FieldHelper.cnName.Amount, "Amount"),
             new OLVColumn("挑战目标", "TargetAmount"),

               new OLVColumn("年会", "IsYearMeeting "),
               new OLVColumn("职级", "StaffLevel "),
             new OLVColumn(FieldHelper.cnName.EntryDate, "EntryDate")
            {
                Width = 110,
                AspectToStringFormat = "{0:d}",
                 ClusteringStrategy = new DateTimeClusteringStrategy(DateTimePortion.Day| DateTimePortion.Month| DateTimePortion.Year, "yyyy-MM-dd"),
             AspectToStringConverter =
                new AspectToStringConverterDelegate(item => item==null||item==DBNull.Value ? "" :Convert.ToDateTime(item).ToString("yyyy-MM-dd")),
            },
              

            new OLVColumn(FieldHelper.cnName.ExitDate, "ExitDate")
            {
                Width = 110,
                AspectToStringFormat = "{0:d}",
                ClusteringStrategy = new DateTimeClusteringStrategy(DateTimePortion.Day | DateTimePortion.Month | DateTimePortion.Year, "yyyy-MM-dd"),

                AspectToStringConverter =
                new AspectToStringConverterDelegate(item => item==null||item==DBNull.Value||Convert.ToDateTime(item)==Staff.DefaultExitDate ? "" :Convert.ToDateTime(item).ToString("yyyy-MM-dd")),},
            });

           
        }
        IEnumerable mySource;

        protected override IEnumerable MySource
        {
            get
            {
                return mySource;
            }
        }
        protected override void FetchData()
        {
            var timeNow=DateTime.Now;
          
          var  sqlTxt = string.Format(
@"
Select
Staff.CompanyName
,Staff.CenterName
,Staff.DepartmentName
,Billing.StaffName as StaffName
,Staff.TargetAmount
,Staff.IsNormal
,Staff.StaffLevel
,Staff.IsYearMeeting
,Staff.EntryDate
,Staff.ExitDate
,case Staff.Sex when 1 then '男' when 0 then '女' else '' end as StaffSex
, {0} - YEAR(Staff.BirthDay) as StaffAge
,Sum(Billing.Amount) as Amount
from Billing 
Left join Staff on 
 Billing.StaffName=Staff.Name
Where Billing.TransDate>='{1}'
And Billing.TransDate<='{2}'
and Billing.HasDeleted=0

Group by 
Staff.CompanyName
,Staff.CenterName
,Staff.DepartmentName
,Staff.IsNormal
,Staff.StaffLevel
,Staff.IsYearMeeting
,Staff.EntryDate
,Staff.ExitDate
,case Staff.Sex when 1 then '男' when 0 then '女' else ''  end 
,{0} - YEAR(Staff.BirthDay) 
,Billing.StaffName
,Staff.TargetAmount
", timeNow.Year, this.dateBeginCell.DateTimePicker.Value.ToString("yyyy-MM-dd"),
 this.dateEndCell.DateTimePicker.Value.ToString("yyyy-MM-dd"));
       
           var dt = BinDataTableAdapter.GetDataTable(CommandType.Text, sqlTxt);

           
            mySource =dt.DefaultView;
        
        }

     
    }

}
